﻿using System;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Diagnostics;

namespace DBLookup
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        DateTime _dtNow;
               
        #region Winform Control Events

        /// <summary>
        /// Page load method
        /// </summary>
        private void Form1Load(object sender, EventArgs e)
        {
            //add connection string if they exist
            if (File.Exists("connections.txt"))
            {
                using (TextReader tr = File.OpenText("connections.txt"))
                {
                    foreach (var line in tr.ReadToEnd().Split(
                        new[]
                        {
                            (char)13,
                            (char)10
                        }).Where(line => line != ""))
                    {
                        comboConnectString.Items.Add(line);
                    }

                }
            }

            splitContainer2.Panel1Collapsed = true;
        }
        
        /// <summary>
        /// code run when 'Execute' button is clicked
        /// </summary>
        private void ExecuteButtonClick(object sender, EventArgs e)
        {
            ExecuteSql();
        }
        
        /// <summary>
        /// Connection string dropdown
        /// </summary>
        private void ComboConnectStringSelectedValueChanged(object sender, EventArgs e)
        {
             if (comboConnectString.Text != "")
             {
                 splitContainer2.Panel1Collapsed = !BuildDatabaseView();
                 Console.Beep();
             }
        }

        /// <summary>
        /// Right click options from datagrid
        /// </summary>
        private void DataGridView1MouseDown(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                var ht = dataGridView1.HitTest(e.X, e.Y);

                //Checks for correct column index
                if (ht.ColumnIndex == -1 && ht.RowIndex == -1)
                {
                    //Create the ContextStripMenu for Creating the PO Sub Form
                    var menu = new ContextMenuStrip();
                    var menuClip1 = new ToolStripMenuItem("Copy to Clipboard");
                    var menuClip2 = new ToolStripMenuItem("Export to Text");

                    menuClip1.MouseDown += ExportClipboard;
                    menuClip2.MouseDown += ExportCsv;

                    menu.Items.AddRange(new ToolStripItem[] { menuClip1, menuClip2 });

                    //Assign created context menu strip to the DataGridView
                    dataGridView1.ContextMenuStrip = menu;
                }

                else
                    dataGridView1.ContextMenuStrip = null;
            }
        }
        
        /// <summary>
        /// allow ctrl+e to run sql
        /// </summary>
        private void SqlKeyDown(object sender, KeyEventArgs e)
        {
            if (e.Control && e.KeyCode == Keys.E)
            {
                //this.Enabled = false;
                //backgroundWorker1.RunWorkerAsync();
                ExecuteSql();
            }
            else if (e.Control && e.KeyCode == Keys.A)
            {
                sql.SelectAll();
            }
        }

        /// <summary>
        /// on enter get the sql structure if possible
        /// </summary>
        private void ComboConnectStringKeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                if (comboConnectString.Text != "")
                {
                    splitContainer2.Panel1Collapsed = !BuildDatabaseView();
                    Console.Beep();
                }
            }
        }
                
        #endregion

        #region Background Worker SQL Process

        /// <summary>
        /// background work to run sql, run in a separate thread
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void BackgroundWorker1DoWork(object sender, DoWorkEventArgs e)
        {
            var results = new ExecuteResults();
            var param = (ExecuteParameters)e.Argument;
 
            try
            {
                // if provider is not specified use sqlconncetion
                if (!param.ConnectionString.ToLower().Contains("provider=") && !param.ConnectionString.ToLower().Contains("provider ="))
                {
                    using (var c = new SqlConnection(param.ConnectionString))
                    {
                        c.Open();


                        using (var a = new SqlDataAdapter(
                            param.SqlStatement, c))
                        {
                            var t = new DataTable();
                            a.Fill(t);
                            // 4
                            // Render data onto the screen
                            //dataGridView1.DataSource = t
                            results.ResultsData = t;
                        }
                    }
                }
                else
                {
                    using (var c = new OleDbConnection(param.ConnectionString))
                    {
                        c.Open();

                        using (var a = new OleDbDataAdapter(
                            param.SqlStatement, c))
                        {
                            var t = new DataTable();
                            a.Fill(t);
                            // 4
                            // Render data onto the screen
                            //dataGridView1.DataSource = t;   
                            results.ResultsData = t;
                        }
                    }
                }

                //Console.Beep();
                results.StatusMessage = "Completed in " + (DateTime.Now - _dtNow) + ". ";

            }
            catch (Exception ex)
            {
                results.ExceptionDetails = ex;
                results.StatusMessage = "Completed with errors in " + (DateTime.Now - _dtNow) + ". ";
            }

            if (results.ResultsData != null)
                results.StatusMessage = results.StatusMessage + results.ResultsData.Rows.Count + " row(s) returned. ";

            e.Result = results;
        }

        /// <summary>
        /// backgound work to run sql complete
        /// </summary>
        private void BackgroundWorker1RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            var results = (ExecuteResults)e.Result;

            if (results.ExceptionDetails != null)
            {
                var exceptionTable = new DataTable();

                exceptionTable.Columns.Add("Exception", typeof(String));
                exceptionTable.Columns.Add(" ", typeof(String));

                exceptionTable.Rows.Add("Message", results.ExceptionDetails.Message);
                exceptionTable.Rows.Add("StackTrace", results.ExceptionDetails.StackTrace);
                exceptionTable.Rows.Add("Source", results.ExceptionDetails.Source);
                exceptionTable.Rows.Add("Data", results.ExceptionDetails.Data);

                //add inner exception if any
                if (results.ExceptionDetails.InnerException == null)
                {
                    exceptionTable.Rows.Add("Inner Exception", "null");
                }
                else
                {
                    exceptionTable.Rows.Add("Inner Exception Message", results.ExceptionDetails.InnerException.Message);
                    exceptionTable.Rows.Add("Inner Exception StackTrace", results.ExceptionDetails.InnerException.StackTrace);
                    exceptionTable.Rows.Add("Inner Exception Source", results.ExceptionDetails.InnerException.Source);
                    exceptionTable.Rows.Add("Inner Exception Data", results.ExceptionDetails.InnerException.Data);
                }

                dataGridView1.DataSource = exceptionTable;
                toolStripStatusLabel1.Text = results.StatusMessage;
            }
            else
            {
                dataGridView1.DataSource = results.ResultsData;
                toolStripStatusLabel1.Text = results.StatusMessage;
            }
            timer1.Enabled = false;
            toolStripStatusLabel3.Text = "";

            // log history
            using (var file = new StreamWriter(
                Path.GetFileNameWithoutExtension(Process.GetCurrentProcess().MainModule.FileName) + ".log", true))
            {
                file.WriteLine(results.StatusMessage);
                if (results.ResultsData != null)
                {
                    file.WriteLine("Returned " + results.ResultsData.Rows.Count + " rows");
                }

                if (results.ExceptionDetails != null)
                {
                    file.WriteLine("Exception details");
                    foreach (DataGridViewRow row in dataGridView1.Rows)
                    {
                        file.WriteLine(row.Cells[0].Value + " : " + row.Cells[1].Value);
                    }
                }

                file.WriteLine();
            }

            dataGridView1.AutoResizeColumns();
            dataGridView1.AutoResizeRows();

            Cursor = Cursors.Default;
            executeButton.Enabled = true;
        }

        private void BackgroundWorker1ProgressChanged(object sender, ProgressChangedEventArgs e)
        {

        }
       
        #endregion

        #region Statusbar Methods

         /// <summary>
        /// Opens the log in notepad
        /// </summary>
        private void ToolStripStatusLabel2Click(object sender, EventArgs e)
        {
            try
            {
                Process.Start(
                    "notepad.exe", 
                    Path.GetFileNameWithoutExtension(Process.GetCurrentProcess().MainModule.FileName) + ".log");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        
        /// <summary>
        /// Status bar message update, enabled during query execution
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Timer1Tick(object sender, EventArgs e)
        {
            toolStripStatusLabel3.Text = @"has been running for " + (DateTime.Now - _dtNow).Seconds + @" seconds";
        }

        #endregion
        
        #region TreeView Methods

        /// <summary>
        /// Populates the treeview, this is executed when the connection string dropdown is updated
        /// </summary>
        /// <returns></returns>
        bool BuildDatabaseView()
        {
            var connectionstring = comboConnectString.Text;

            if (connectionstring.ToLower().Contains("provider=") || connectionstring.ToLower().Contains("provider ="))
                return false;

            try
            {

                using (var sqlConnection = new SqlConnection(connectionstring + ";Connection Timeout=3"))
                {
                    sqlConnection.Open();
                    //todo Views are currently excluded, possible enhancement to return VIEW_DEFINITION rows in new group
                    using (var sqlCommand = new SqlCommand(
                        @"  SELECT a.TABLE_CATALOG, 
                                   a.TABLE_SCHEMA + '.' + a.TABLE_NAME AS [TABLE_NAME], 
                                   a.COLUMN_NAME + ' (' + a.DATA_TYPE + ')' AS [COLUMN_NAME]
                            FROM INFORMATION_SCHEMA.COLUMNS a
	                            LEFT OUTER JOIN INFORMATION_SCHEMA.VIEWS b ON a.TABLE_CATALOG = b.TABLE_CATALOG
		                            AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
		                            AND a.TABLE_NAME = b.TABLE_NAME
                            WHERE a.TABLE_NAME <> '__RefactorLog' AND b.VIEW_DEFINITION IS NULL
                            ORDER BY a.TABLE_CATALOG, a.TABLE_NAME, a.ORDINAL_POSITION", sqlConnection))
                    {

                        using (var sqlReader = sqlCommand.ExecuteReader())
                        {
                            if (!sqlReader.HasRows)
                                return false;

                            treeView1.Nodes.Clear();
                            var databaseNode = new TreeNode("Unknown");
                            treeView1.HotTracking = false;
                            var lastTableName = "";
                            while (sqlReader.Read())
                            {

                                // Set table
                                if (databaseNode.Text == @"Unknown") databaseNode.Text = sqlReader.GetString(0);

                                // add new table if different from last
                                var tableName = sqlReader.GetString(1);
                                if (tableName != lastTableName)
                                {
                                    lastTableName = tableName;
                                    var tableNode = new TreeNode(tableName)
                                    {
                                        ContextMenuStrip = contextMenuStripTable
                                    };
                                    databaseNode.Nodes.Add(tableNode);
                                }

                                //add the cols
                                databaseNode.LastNode.Nodes.Add(sqlReader.GetString(2));

                            }
                            treeView1.Nodes.Add(databaseNode);

                            treeView1.Nodes[0].Expand();
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                toolStripStatusLabel1.Text = ex.Message;
                return false;
            }

            sql.Focus();
            return true;
        }

        /// <summary>
        /// Private helper method used in all TreeView methods to add the text to the sqlbox
        /// </summary>
        /// <param name="s"></param>
        void AppendToSqlBox(string s)
        {
            var selectStart = sql.SelectionStart;
            
            sql.Text =
                sql.Text.Substring(0, selectStart) + s + sql.Text.Substring(selectStart);

            sql.Select(selectStart, s.Length);
            sql.Focus();

        }

        /// <summary>
        /// This is requred to make sure the right click will select the correct node
        /// </summary>
        private void TreeView1MouseDown(object sender, MouseEventArgs e)
        {
            treeView1.SelectedNode = treeView1.GetNodeAt(e.X, e.Y);
        }

        /// <summary>
        /// Copys the node into the sql box
        /// </summary>
        private void TreeView1MouseDoubleClick(object sender, MouseEventArgs e)
        {
            var node = treeView1.SelectedNode;
            AppendToSqlBox(node.Text.Contains("(") ? 
                Helper.FormatColumnName(node) : Helper.FormatTableName(node));
        }

        /// <summary>
        /// Constructs a SELECT TOP 500  query
        /// </summary>
        private void ToolStripMenuItem1Click(object sender, EventArgs e)
        {
            var builder = new StringBuilder();            
            builder.Append("SELECT TOP 500");
            foreach (TreeNode node in treeView1.SelectedNode.Nodes)
            {
                if (builder.ToString() == "SELECT TOP 500")
                {
                    builder.AppendLine();
                    builder.AppendLine("    " + Helper.FormatColumnName(node));
                }
                else
                {
                    builder.AppendLine("   ," + Helper.FormatColumnName(node));
                }
            }
            builder.AppendLine("FROM " + Helper.FormatTableName( treeView1.SelectedNode ));
            builder.AppendLine();

            AppendToSqlBox(builder.ToString());

            ExecuteSql();
        }

        /// <summary>
        /// Constructs a SELECT query
        /// </summary>
        private void SElectToolStripMenuItemClick(object sender, EventArgs e)
        {
            var builder = new StringBuilder();

            builder.Append("SELECT ");

            foreach (TreeNode node in treeView1.SelectedNode.Nodes)
            {
                if (builder.ToString() == "SELECT ")
                    builder.AppendLine(" " + Helper.FormatColumnName(node));
                else
                    builder.AppendLine("       ," + Helper.FormatColumnName(node) );
            }

            builder.AppendLine("FROM " + Helper.FormatTableName(treeView1.SelectedNode));
            builder.AppendLine();

            AppendToSqlBox(builder.ToString());
        }

        /// <summary>
        /// Constructs a INSERT query
        /// </summary>
        private void NsertToolStripMenuItemClick(object sender, EventArgs e)
        {
            var builder = new StringBuilder();
            var sqlValues = new StringBuilder();
            
            builder.Append("INSERT INTO ");
            sqlValues.AppendLine("VALUES ");

            foreach (TreeNode node in treeView1.SelectedNode.Nodes)
            {
                if (builder.ToString() == "INSERT INTO ")
                {
                    builder.AppendLine(Helper.FormatTableName(treeView1.SelectedNode));
                    builder.AppendLine("  (");
                    builder.AppendLine("    " + Helper.FormatColumnName(node));
                    sqlValues.AppendLine("  (");
                    sqlValues.AppendLine("    <" + node.Text + ">");
                }
                else
                {    
                    builder.AppendLine("   ," + Helper.FormatColumnName(node));
                    sqlValues.AppendLine("   ,<" + node.Text + ">");
                }
            }

            builder.AppendLine("  )");
            sqlValues.AppendLine("  )");
            sqlValues.AppendLine();

            AppendToSqlBox(builder + sqlValues.ToString() );
        }

        /// <summary>
        /// Constructs a UPDATE query
        /// </summary>
        private void UPdateToolStripMenuItemClick(object sender, EventArgs e)
        {
            var builder = new StringBuilder();

            builder.Append("UPDATE ");

            foreach (TreeNode node in treeView1.SelectedNode.Nodes)
            {
                if (builder.ToString() == "UPDATE ")
                {
                    builder.AppendLine(Helper.FormatTableName(treeView1.SelectedNode));
                    builder.AppendLine(" SET " + Helper.FormatColumnName(node)  + " = <" + node.Text + ">");
                }
                else
                {
                    builder.AppendLine("    ," + Helper.FormatColumnName(node) + " = <" + node.Text + ">");
                }
            }

            builder.AppendLine("WHERE <Search Conditions>");
            builder.AppendLine();

            AppendToSqlBox(builder.ToString());

        }

        /// <summary>
        /// Constructs a DELETE query
        /// </summary>
        private void DEleteToolStripMenuItemClick(object sender, EventArgs e)
        {          
             var builder = new StringBuilder();

             builder.AppendLine("DELETE FROM " + Helper.FormatTableName(treeView1.SelectedNode));
            builder.AppendLine("WHERE <Search Conditions>");
 
            AppendToSqlBox(builder.ToString());
            builder.AppendLine();

        }
        
        #endregion
        
        #region Private Methods

        void ExecuteSql()
        {
            //create history of conneciton string
            if (comboConnectString.Items.Count == 0 || !comboConnectString.Items.Contains(comboConnectString.Text))
            {
                comboConnectString.Items.Insert(0, comboConnectString.Text);
            }

            executeButton.Enabled = false;
            Cursor = Cursors.WaitCursor;
            _dtNow = DateTime.Now;
            toolStripStatusLabel1.Text = @"Execute started at " + _dtNow + @". ";
            timer1.Enabled = true;

            var connectionstring = comboConnectString.Text;
            
            //pick select sql first if empty then take all
            var sSql = sql.SelectedText;
            if (sSql == "") sSql = sql.Text;

            // log history
            using (var file = new StreamWriter(
                Path.GetFileNameWithoutExtension(Process.GetCurrentProcess().MainModule.FileName) + ".log", true))
            {
                file.WriteLine(toolStripStatusLabel1.Text);
                file.WriteLine("Connection : " + Helper.RemoveConnectionStringSecurity(connectionstring));
                file.WriteLine("SQL : " + sSql);
            }

            // run sql in background worker
            backgroundWorker1.RunWorkerAsync(new ExecuteParameters
            {
                ConnectionString = connectionstring,
                //Provider = comboBox1.SelectedItem.ToString(), 
                SqlStatement = sSql
            });

        }

        void ExportClipboard(object sender, MouseEventArgs e)
        {
            dataGridView1.SelectAll();
            var dataObj = dataGridView1.GetClipboardContent();
            if (dataObj != null)
                Clipboard.SetDataObject(dataObj);

        }

        void ExportCsv(object sender, MouseEventArgs e)
        {
            //test to see if the DataGridView has any rows
            if (dataGridView1.RowCount > 0)
            {

                var saveDialog = saveFileDialog1.ShowDialog();
                if (saveDialog != DialogResult.OK) return;

                var swOut = new StreamWriter(saveFileDialog1.FileName);

                //write header rows to csv
                for (var i = 0; i <= dataGridView1.Columns.Count - 1; i++)
                {
                    if (i > 0)
                    {
                        swOut.Write(",");
                    }
                    swOut.Write(dataGridView1.Columns[i].HeaderText);
                }

                swOut.WriteLine();

                //write DataGridView rows to csv
                for (var j = 0; j <= dataGridView1.Rows.Count - 1; j++)
                {
                    if (j > 0)
                    {
                        swOut.WriteLine();
                    }

                    var dr = dataGridView1.Rows[j];

                    for (var i = 0; i <= dataGridView1.Columns.Count - 1; i++)
                    {
                        if (i > 0)
                        {
                            swOut.Write(",");
                        }

                        var value = dr.Cells[i].Value.ToString();
                        //replace comma's with spaces
                        value = value.Replace(',', ' ');
                        //replace embedded newlines with spaces
                        value = value.Replace(Environment.NewLine, " ");

                        swOut.Write(value);
                    }
                }
                swOut.Close();
            }
            else
            {
                MessageBox.Show(@"Nothing to export");
            }
        }

        #endregion

    }
}
